package com.example.studytest.Repository.Test4;


import com.example.studytest.Entity.Test4.Record_score_info;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.List;

public interface Record_score_infoRepository extends JpaRepository<Record_score_info,Integer> {
    @Query(value = "SELECT a.* \n" +
            "FROM record_score_info as a INNER JOIN t_invite as b ON a.invited_code = b.code\n" +
            "where b.r_email =:remail",nativeQuery = true)
    List<Record_score_info> getRSInfoByR_email(@Param("remail") String remail);

    @Transactional
    @Modifying(clearAutomatically = true)
    @Query(value = "INSERT INTO record_score_info(id,exp_date,invited_code,session)\n" +
            "SELECT b.id, b.date, c.invited_code, b.session FROM record_score b INNER JOIN t_user c ON b.email = c.email INNER JOIN t_invite d on c.invited_code = d.code\n" +
            "WHERE d.r_email =:remail",nativeQuery = true)
    int insertInfoByRemail(@Param("remail") String remail);

    @Query(value = "SELECT * FROM record_score_info a INNER JOIN t_user b on a.invited_code = b.invited_code\n" +
            "where b.email=:email",nativeQuery = true)
    List<Record_score_info> getRSInfoByEmail(@Param("email") String email);

    @Query(value = "SELECT a.* FROM record_score_info a INNER JOIN t_user b on a.invited_code = b.invited_code " +
            "where b.email =:email AND a.exp_date =:date",nativeQuery = true)
    List<Record_score_info> getRSInfoByDate(@Param("date") String date, @Param("email") String email);



}
